﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using EmoneyWeiXinCommon;
using EmoneyWeiXinEntity;
using System.Data;

namespace EmoneyWeiXinDAL
{
    public class Active910DAL
    {
        public static void InsertShareRecord(string actName, string openid, int shareType)
        {
            string sql = "INSERT INTO ShareRecord(openid,ActName,sharaType,createTime) values(@openid,@ActName,@sharaType,GETDATE());"; //添加分享记录
            string sql2 = "update ActiveDataFor910 set is2Chance=1 where is2Chance=0 AND openid=@openid AND isJackpot=0"; //分享之后获取重新抽奖的机会,满足条件（没有中奖，并且没有参与第二次抽奖）
            sql = sql + sql2;
            SqlParameter[] paras = 
            {
              new SqlParameter("@openid",openid),
              new SqlParameter("@actName",actName),
              new SqlParameter("@sharaType",shareType)
            };
            SQLHelper.ExecuteNonQuery(Config.EmoneyWeiXin, System.Data.CommandType.Text, sql, paras);

        }
        /// <summary>
        /// 根据openid 获取他的活动数据
        /// </summary>
        /// <param name="openid"></param>
        /// <returns></returns>
        public static ActiveDataFor910Entity GetActiveData910(string openid)
        {
            string sql = "SELECT * FROM ActiveDataFor910 with(nolock) where openid=@openid";
            SqlParameter[] paras = 
            {
              new SqlParameter("@openid",openid)
            };
            return SQLHelper.GetDataByColumns<ActiveDataFor910Entity>(Config.EmoneyWeiXin, sql, paras);
        }
        /// <summary>
        /// 插入一条活动结果数据
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public static int InsertNewActiveData(ActiveDataFor910Entity entity, string ActName, int isHaveRedBag, double t_money, int isSecond)
        {
            string sql = "AddActiveDataFor910";
            SqlParameter[] paras = 
            {
               new SqlParameter("@openid",entity.openid),
               new SqlParameter("@gift",entity.gift),
               new SqlParameter("@isJackpot",entity.isJackpot),
               new SqlParameter("@ActivityName",ActName),
               new SqlParameter("@t_money",t_money),
               new SqlParameter("@isHaveRedBag",isHaveRedBag),
               new SqlParameter("@isSecond",isSecond)
            };
            return SQLHelper.ExecuteNonQuery(Config.EmoneyWeiXin, System.Data.CommandType.StoredProcedure, sql, paras);
        }

        /// <summary>
        /// 插入一条活动数据，并返回当前记录的ID
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="lastId"></param>
        public static void InsertNewActiveData(ActiveDataFor910Entity entity, out object lastId)
        {
            string sql = "insert into ActiveDataFor910 values(@openid,@gift,@isJackpot,0,null,GETDATE());SELECT @@IDENTITY";
            SqlParameter[] paras = 
            {
               new SqlParameter("@openid",entity.openid),
               new SqlParameter("@gift",entity.gift),
               new SqlParameter("@isJackpot",entity.isJackpot)
            };
            object obj = SQLHelper.ExecuteScalar(Config.EmoneyWeiXin, System.Data.CommandType.Text, sql, paras);
            lastId = obj;
        }

        public static int InsertAddressInfo(AddressInfoFor910ActEntity entity)
        {
            string sql = "insert into AddressInfoFor910Act values(@openid,@AName,@AddressInfo,@phone,@isGumin,GETDATE())";
            SqlParameter[] paras = 
            {
              new SqlParameter("@AName",entity.AName),
              new SqlParameter("@AddressInfo",entity.AddressInfo),
              new SqlParameter("@phone",entity.phone),
              new SqlParameter("@isGumin",entity.isGumin),
              new SqlParameter("@openid",entity.openid)
            };
            return SQLHelper.ExecuteNonQuery(Config.EmoneyWeiXin, System.Data.CommandType.Text, sql, paras);
        }




        public static DataTable GetTJData()
        {
            string sql = @"select 
(select COUNT(1) from ActiveDataFor910 with(nolock)) zcyrs ,--总参与人数
(select COUNT(1) from ShareRecord with(nolock) where sharaType=0  AND  ActName='NineMonth') fxdpyq,--分享到朋友圈次数
(select COUNT(1) from ShareRecord with(nolock) where sharaType=1  AND  ActName='NineMonth') fxgpy,--分享给朋友次数
(select COUNT(1) from ActiveDataFor910 with(nolock) where gift=5) getLaTiao,--获得辣条的数量
(select COUNT(1) from ActiveDataFor910 with(nolock) where gift=4) getDanGOng,--获得弹弓的数量
(select COUNT(1) from ActiveDataFor910 with(nolock) where gift=2) getTiaoTiaoTang,--获得跳跳糖的数量
(select COUNT(1) from ActiveDataFor910 with(nolock) where gift=1) getDaLibao,--获得大礼包的数量
(select COUNT(1) from ActiveDataFor910 with(nolock) where gift=3) getNo,--没中奖的数量
(select ISNULL( SUM(t_Money),0) from RedBag with(nolock)  where ActivityName='NineMonth') TotalMoney,--没中奖的数量
(select ISNULL( SUM(GettedMoney),0) from RedBag with(nolock)  where ActivityName='NineMonth') TotalGetMoney--没中奖的数量
";
            return SQLHelper.ExecuteGetTable(Config.EmoneyWeiXin, System.Data.CommandType.Text, sql);
        }
        /// <summary>
        /// 获取到总花费金额
        /// </summary>
        /// <returns></returns>
        public static double GetSumMoney()
        {
            string sql = "select ISNULL( SUM(t_Money),0) from dbo.RedBag where ActivityName='NineMonth'";
            return Convert.ToDouble( SQLHelper.ExecuteScalar(Config.EmoneyWeiXin, System.Data.CommandType.Text, sql, null));
        }


    }
}
